ALTER TABLE dbo.Approval
ADD Requester varchar(200)
ALTER TABLE dbo.Approval
ADD RequestDate datetime

//tabel master
ALTER TABLE dbo.[NAS]
ADD IsDeleted bit
ALTER TABLE dbo.[Operating System]
ADD IsDeleted bit
ALTER TABLE dbo.[Printer]
ADD IsDeleted bit
ALTER TABLE dbo.[Provider]
ADD IsDeleted bit
ALTER TABLE dbo.[Router]
ADD IsDeleted bit
ALTER TABLE dbo.[System Brand]
ADD IsDeleted bit
ALTER TABLE dbo.[System Type]
ADD IsDeleted bit
ALTER TABLE dbo.[Vendor]
ADD IsDeleted BIT

--Per 18 Desember
ALTER TABLE dbo.[CR Physical]
ADD ID_Physical UNIQUEIDENTIFIER NULL

--Per 8 Januari
ALTER TABLE dbo.[CR Network]F
ADD RequestDetail VARCHAR(max), AffectedDevice varchar(MAX) ,ErrorPossibility varchar(MAX)

--Per 13 Januari
CREATE TABLE [dbo].[Rack Number](
	[ID] [uniqueidentifier] NOT NULL,
	[Title] [varchar](200) NULL,
 CONSTRAINT [PK_RackNumber] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Location](
	[ID] [uniqueidentifier] NOT NULL,
	[Kode] [varchar](200) NULL,
	[Nama] [varchar](200) NULL,
 CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE dbo.[System Type]
ADD Size int
GO

--Per 16 Januari
ALTER TABLE dbo.[Inventory Network]
ADD URLAttachment varchar(200)
ALTER TABLE dbo.[Inventory Physical]
ADD URLAttachment varchar(200)
ALTER TABLE dbo.[Inventory Storage]
ADD URLAttachment varchar(200)
ALTER TABLE dbo.[Inventory VM]
ADD URLAttachment varchar(200)
ALTER TABLE dbo.[Work Order]
ADD URLAttachment varchar(200)
ALTER TABLE dbo.[Work Order]
ADD Name varchar(200)

--Per 18 Januari
CREATE TABLE [dbo].[Deployment Printer](
	[ID] [uniqueidentifier] NOT NULL,
	[ID_Printer] [uniqueidentifier] NULL,
	[JmlPrinter] [int] NULL,
	[ID_DataKunjungan] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Deployment Printer] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Deployment Printer]  WITH CHECK ADD  CONSTRAINT [FK_Deployment Printer_Data Kunjungan] FOREIGN KEY([ID_DataKunjungan])
REFERENCES [dbo].[Data Kunjungan] ([ID])
GO

ALTER TABLE [dbo].[Deployment Printer] CHECK CONSTRAINT [FK_Deployment Printer_Data Kunjungan]
GO

-- Buat Table Deployment Unit dengan column2 baru
DROP TABlE [Deployment Unit] 
CREATE TABLE [dbo].[Deployment Unit](
	[ID] [uniqueidentifier] NOT NULL,
	[JmlDesktop] [int] NULL,
	[JmlNotebook] [int] NULL,
	[JmlThinClient] [int] NULL,
	[ID_DataKunjungan] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Deployment Unit] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Deployment Unit]  WITH CHECK ADD  CONSTRAINT [FK_Deployment Unit_Data Kunjungan] FOREIGN KEY([ID_DataKunjungan])
REFERENCES [dbo].[Data Kunjungan] ([ID])
GO

ALTER TABLE [dbo].[Deployment Unit] CHECK CONSTRAINT [FK_Deployment Unit_Data Kunjungan]
GO

-- Tambah Kolom untuk Data Kunjungan
ALTER TABLE dbo.[Data Kunjungan]
ADD DeskripsiLain2 varchar(200)


--Per 19 Januari
ALTER PROCEDURE [dbo].[sp_Select_Task]
AS
SELECT a.*,b.wfStatus FROM dbo.Approval a INNER JOIN dbo.[Data Cabang] b ON a.IDChild = b.ID
WHERE b.wfStatus LIKE 'waiting for approval'
UNION
--tambahan untuk data kunjungan
SELECT a.*,b.wfStatus FROM dbo.Approval a INNER JOIN dbo.[Data Kunjungan] b ON a.IDChild = b.ID
WHERE b.wfStatus LIKE 'waiting for approval'
UNION
SELECT a.*,b.wfStatus FROM dbo.Approval a INNER JOIN dbo.[CR Storage] b ON a.IDChild = b.ID
WHERE b.wfStatus LIKE 'waiting for approval'
UNION
SELECT a.*,b.wfStatus FROM dbo.Approval a INNER JOIN dbo.[CR Network] b ON a.IDChild = b.ID
WHERE b.wfStatus LIKE 'waiting for approval'
UNION
SELECT a.*,b.wfStatus FROM dbo.Approval a INNER JOIN dbo.[CR VM] b ON a.IDChild = b.ID
WHERE b.wfStatus LIKE 'waiting for approval'
UNION
SELECT a.*,b.wfStatus FROM dbo.Approval a INNER JOIN dbo.[CR Physical] b ON a.IDChild = b.ID
WHERE b.wfStatus LIKE 'waiting for approval'
